package com.property.manage.service.flowingWater;


import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.guardchina.framework.mybatis.model.CustomPage;
import com.guardchina.framework.util.exception.BusinessException;
import com.guardchina.framework.util.exception.SystemException;
import com.guardchina.framework.util.utils.DateUtil;
import com.guardchina.framework.util.utils.NumberToCn;
import com.guardchina.framework.util.utils.NumberUtil;
import com.property.manage.dao.flowingWater.ArrearsDetailMapper;
import com.property.manage.dao.flowingWater.FlowingWaterExpandMapper;
import com.property.manage.dao.flowingWater.FlowingWaterMapper;
import com.property.manage.enums.WalletType;
import com.property.manage.model.flowingWater.*;
import com.property.manage.model.owner.OwnerEntity;
import com.property.manage.model.print.PayBillVo;
import com.property.manage.model.resource.Building;
import com.property.manage.model.resource.BuildingVo;
import com.property.manage.model.resource.ProBuildingVo;
import com.property.manage.model.system.SysSet;
import com.property.manage.model.system.User;
import com.property.manage.model.village.Village;
import com.property.manage.service.fee.PropertyService;
import com.property.manage.service.owner.OwnerService;
import com.property.manage.service.renovation.DepositService;
import com.property.manage.service.rent.RentService;
import com.property.manage.service.resource.BuildingService;
import com.property.manage.service.system.SnowflakeIdWorker;
import com.property.manage.service.system.SysSetService;
import com.property.manage.service.system.UserService;
import com.property.manage.service.village.VillageService;
import com.property.manage.util.DateUtilPlus;
import com.property.manage.util.ExportUtil;
import io.swagger.models.auth.In;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;

/**
 * 描述
 *
 * @author lwb
 * @since 2020/7/15
 */
@Service
@Slf4j
public class FlowingWaterService {
    @Autowired
    private FlowingWaterMapper flowingWaterMapper;
    @Autowired
    @Lazy
    private PropertyService propertyService;
    @Autowired
    private RentService rentService;
    @Autowired
    private VillageService villageService;
    @Autowired
    private WalletService walletService;
    @Autowired
    private UserService userService;
    @Autowired
    private BuildingService buildingService;
    @Autowired
    private DepositService depositService;
    @Autowired
    private SysSetService sysSetService;

    private SnowflakeIdWorker snowflakeIdWorker;

    private OwnerService ownerService;

    @Autowired
    private ArrearsDetailService arrearsDetailService;

    @Resource
    private ArrearsDetailMapper arrearsDetailMapper;

    @Value("${timed.patee}")
    private String patee;

    @Value("${timed.receiveUnit}")
    private String receiveUnit;

    @Value("${daily.running.water}")
    private String water;
    @Value("${daily.running.building}")
    private String building;
    @Value("${daily.running.statistics}")
    private String statistics;

    @Value("${daily.running.monthStatistic}")
    private String monthStatisticTemplate;


    public CustomPage<FlowingWaterVo> page(Long pageNo, Long pageSize, Integer villageId, Integer building,
                                           Integer unit, String roomNumber, String startTime, String endTime) {
        Page<FlowingWaterVo> page = new Page<>(pageNo, pageSize);
        IPage<FlowingWaterVo> village = flowingWaterMapper.getPage(page, villageId, startTime, endTime, building, unit, roomNumber);
        List<FlowingWaterVo> records = village.getRecords();
        if (!records.isEmpty()) {
            for (FlowingWaterVo record : records) {
                if (record.getBuildingId() != null) {
                    String address = buildingService.findAddress(record.getBuildingId());
                    record.setBuildingStr(address);
                }
                record.setChargeTypeStr(sysSetService.getDesc(SysSet.COLLECTION_TYPE, record.getChargeType().toString()));
            }
        }
        return new CustomPage<>(village);
    }

    public CustomPage<FlowingWaterVo> wuyePage(Long pageNo, Long pageSize, Integer villageId, String startTime, String endTime, String billNo,
                                               String building, String unit, String roomNumber
    ) {
        Page<FlowingWaterVo> page = new Page<>(pageNo, pageSize);
        IPage<FlowingWaterVo> village = flowingWaterMapper.getWuYePage(page, villageId, startTime, endTime, billNo, building, unit, roomNumber);
        List<FlowingWaterVo> records = village.getRecords();
        if (records.isEmpty()) {
            return new CustomPage<>(village);
        }
        for (FlowingWaterVo record : records) {
            String payType = record.getPayType();
            record.setPayTypeName(sysSetService.getDesc(SysSet.PAY_TYPE, payType));
        }
        return new CustomPage<>(village);
    }

    public CustomPage<FlowingWaterVo> getPrePage(Long pageNo, Long pageSize, Integer villageId, String startTime, String endTime, String billNo,
                                                 String building, String unit, String roomNumber
    ) {
        Page<FlowingWaterVo> page = new Page<>(pageNo, pageSize);
        IPage<FlowingWaterVo> village = flowingWaterMapper.getPrePage(page, villageId, startTime, endTime, billNo, building, unit, roomNumber);
        List<FlowingWaterVo> records = village.getRecords();
        if (!records.isEmpty()) {
            for (FlowingWaterVo record : records) {
                record.setPayTypeName(sysSetService.getDesc(SysSet.PAY_TYPE, record.getPayType()));
                if (record.getWalletType() == null || "PROPERTY".equals(record.getWalletType())) {
                    record.setWalletTypeValue("物业费");
                } else if ("RENT".equals(record.getWalletType())) {
                    record.setWalletTypeValue("房屋租金");
                } else if ("PARKING".equals(record.getWalletType())) {
                    record.setWalletTypeValue("车位租金");
                }
            }
        }
        return new CustomPage<>(village);
    }

    public StatisticsVo statistics(Integer villageId, String startTime, String endTime
            , String drawer, String building, String unit, String roomNumber) {
        return flowingWaterMapper.statistics(villageId, startTime, endTime, drawer, building, unit, roomNumber);
    }

    @Transactional(rollbackFor = Exception.class)
    public void deleteWuye(Integer id) {
        FlowingWaterEntity entity = getNormalWuyeById(id);
        if (FlowingWaterEntity.OTHER_TYPE_NORMAL.equals(entity.getOtherType())) {
            deleteWuyeDeduction(entity);
        } else {
            deleteWuyeArrears(entity);
        }
    }

    private FlowingWaterEntity getNormalWuyeById(Integer id) {
        QueryWrapper<FlowingWaterEntity> wrapper = new QueryWrapper<>();
        wrapper.eq("id", id);
        wrapper.ne("type", "3");
        wrapper.eq("charge_type", 1);
        wrapper.eq("state", FlowingWaterEntity.STATE_NORMAL);

        return Optional.ofNullable(flowingWaterMapper.selectOne(wrapper)).orElseThrow(() -> new BusinessException("物业缴费记录不存在", String.format("id: %s", id)));
    }

    private void deleteWuyeDeduction(FlowingWaterEntity entity) {
        walletService.deposit(entity.getBuildingId(), entity.getNet(), null);

        deleteFlowingWater(entity.getId());

        int year = Integer.parseInt(entity.getExplain().substring(0, 4));
        int month = Integer.parseInt(entity.getExplain().substring(4, 6));

        LocalDate startDate = LocalDate.of(year, month, 1);
        LocalDate endDate = startDate.with(TemporalAdjusters.lastDayOfMonth());

        arrearsDetailService.add(entity.getBuildingId(), "物业管理费", entity.getMoney(), entity.getMoney().subtract(entity.getNet()), entity.getNet(), startDate, endDate, "批量划扣删除");
    }

    private void deleteWuyeArrears(FlowingWaterEntity entity) {

        //使用了预存，则把预存金额归还
        if (entity.getPreStorage().compareTo(BigDecimal.ZERO) > 0) {
            walletService.deposit(entity.getBuildingId(), entity.getPreStorage(), null);
        }
        //预存转存，则退还预存
        if (entity.getSmall().compareTo(BigDecimal.ZERO) > 0) {
            walletService.updateExpenditure(entity.getBuildingId(), entity.getSmall(), null);
        }


        deleteFlowingWater(entity.getId());


        arrearsDetailService.undoCharge(entity.getId());

    }


    public PayBillVo addOrUpdate(Integer id, Integer userId, Integer villageId, Integer buildingId, BigDecimal money, Integer type, Integer chargeType, String listOfCharges, String payer, String payee, LocalDate paymentTime, Boolean print, String remark) {
        User user = userService.getUser(userId);
        String billNo;

        FlowingWaterEntity entity;

        if (id != null) {
            entity = getById(id);
            billNo = entity.getBillNo();
        } else {
            entity = new FlowingWaterEntity();
            billNo = snowflakeIdWorker.nextId() + "";
        }

        entity.setSerialNumber(DateUtil.getYYYYMMDD(LocalDate.now()) + NumberUtil.getString());
        entity.setVillageId(villageId);
        entity.setBuildingId(buildingId);
        entity.setMoney(money);
        entity.setNet(money);
        entity.setDeduction(BigDecimal.ZERO);
        entity.setType(type);
        entity.setChargeType(chargeType);
        entity.setListOfCharges(listOfCharges);
        entity.setPayer(payer);
        entity.setPayee(payee);
        entity.setPaymentTime(paymentTime);
        entity.setDrawer(user.getUserName());
        entity.setBillNo(billNo);
        entity.setRemarks(remark);

        if (id == null) {
            flowingWaterMapper.insert(entity);
        } else {
            flowingWaterMapper.updateById(entity);
        }


        if (print) {
            Village village = villageService.getVillage(villageId);
            Building building = null;
            OwnerEntity ownerEntity = null;
            if (buildingId != null) {
                building = buildingService.findBuilding(buildingId);
                ownerEntity = ownerService.findByBuildingId(buildingId);
            }
            PayBillVo payBillVo = new PayBillVo();
            payBillVo.setTitle(patee + "收款收据");

            payBillVo.setBillNo(billNo);

            payBillVo.setVillageName(village.getName());

            if (building != null) {
                payBillVo.setAddress(building.getBuilding() + "-" + building.getUnit() + "-" + building.getRoomNumber());
            }
            if (ownerEntity != null) {
                payBillVo.setOwnerName(ownerEntity.getOwnerName());
            }

            payBillVo.setPayTime(paymentTime);

            payBillVo.setFeeType(sysSetService.getDesc(SysSet.COLLECTION_TYPE, chargeType.toString()));

            payBillVo.setMoney(money);

            payBillVo.setRemark(remark);

            payBillVo.setRealAmount(money);

            payBillVo.setChineseAmount(NumberToCn.number2Cn(money));

            payBillVo.setPayer(payer);
            payBillVo.setPayee(user.getUserName());

            payBillVo.setReceiveUnit(payee);

            return payBillVo;
        }

        return null;
    }

    @Transactional(rollbackFor = Exception.class)
    public Integer add(Integer villageId, Integer relationId, BigDecimal money, BigDecimal net, BigDecimal deduction,
                       String listOfCharges, Integer chargeType, String payer, String payee, Integer type, String preStatus,
                       String usePre, User user, String remarks, Integer buildingId, BigDecimal small, BigDecimal cash, String payType, String billNo, WalletType walletType
    ) {
        FlowingWaterEntity entity = new FlowingWaterEntity();
        entity.setSerialNumber(DateUtil.getYYYYMMDD(LocalDate.now()) + NumberUtil.getString());
        entity.setRelationId(relationId);
        entity.setPayType(payType);
        //合计金额
        entity.setMoney(money);
        //减免金额
        entity.setDeduction(deduction);
        //实收金额
        entity.setNet(net);

        //缴费类型
        entity.setWalletType(walletType.name());

        //使用预存

        if ("1".equals(usePre)) {
            if (buildingId == null) {
                throw new BusinessException("使用预存费用时，楼栋id不能为空");
            }
            //查询剩余金额
            BigDecimal balance = walletService.findBalance(buildingId, walletType);
            //从预存费用中划扣费用  如果预存大于应收金额，则扣除应收 反之
            BigDecimal decimal = new BigDecimal(0);
            if (balance.compareTo(net) > 0) {
                decimal = walletService.updateExpenditure(buildingId, net, walletType);
                entity.setPreStorage(net);
            } else {
                decimal = walletService.updateExpenditure(buildingId, balance, walletType);
                entity.setPreStorage(balance);
            }
            //设置当前预存费用
            entity.setCurrentDeposit(decimal);
            //使用预存
            //使用现金
        } else {
            entity.setPreStorage(new BigDecimal(0));
            if (buildingId != null) {
                BigDecimal balance = walletService.findBalance(buildingId, walletType);
                entity.setCurrentDeposit(balance);
            } else {
                entity.setCurrentDeposit(new BigDecimal(0));
            }
        }
        //收费名目
        entity.setListOfCharges(listOfCharges);
        entity.setCash(cash);
        //收费类型
        entity.setChargeType(chargeType);
        entity.setPayer(payer);
        entity.setPayee(payee);
        entity.setType(type);
        entity.setPaymentTime(LocalDate.now());
        entity.setVillageId(villageId);
        entity.setBuildingId(buildingId);
        entity.setRemarks(remarks);
        entity.setDrawer(user.getUserName());
        entity.setSmall(small);
        //判断是否零钱转存预存
        if ("1".equals(preStatus)) {
            if (buildingId == null) {
                throw new BusinessException("找零转预存时，楼栋id不能为空");
            }
            BigDecimal decimal = walletService.changeDeposit(buildingId, small, walletType);
            entity.setCurrentDeposit(decimal);
        }
        entity.setBillNo(billNo);
        entity.setState(FlowingWaterEntity.STATE_NORMAL);
        flowingWaterMapper.insert(entity);
        return entity.getId();
    }


    private void updateNextTime(Integer relationId, LocalDate nextTime, Integer chargeType) {
        if (relationId != null) {
            //物业管理
            if (chargeType == 1) {
                propertyService.update(relationId, nextTime);
            }
            if (chargeType == 2 || chargeType == 3) {
                rentService.update(relationId, chargeType, nextTime);
            }
        }
    }

    @Transactional(rollbackFor = Exception.class)
    public void update(Integer id, BigDecimal money, LocalDate nextTime, Integer relationId,
                       String listOfCharges, Integer chargeType, String payer, String payee, Integer type,
                       LocalDate paymentTime, String discount) {

        UpdateWrapper<FlowingWaterEntity> wrapper = new UpdateWrapper<>();
        wrapper.set("money", money);
        wrapper.set("next_time", nextTime);
        wrapper.set("list_of_charges", listOfCharges);
        wrapper.set("charge_type", chargeType);
        wrapper.set("payer", payer);
        wrapper.set("payee", payee);
        wrapper.set("type", type);
        wrapper.set("payment_time", paymentTime);
        wrapper.set("discount", discount);
        wrapper.eq("id", id);
        wrapper.set("relation_id", relationId);
        flowingWaterMapper.update(null, wrapper);
        //如果是物业费和租金管理费 有填写下次缴费时间 这更新对应的下次缴费时间
        updateNextTime(relationId, nextTime, chargeType);
    }


    public void delete(Integer id) {
        flowingWaterMapper.deleteById(id);
    }

    public FlowingMoneyVo getMoney(Integer villageId, String startTime, String endTime, Integer building, Integer unit, String roomNumber) {

        FlowingMoneyVo flowingMoneyVo = flowingWaterMapper.getMoney(villageId, startTime, endTime, building, unit, roomNumber);

        flowingMoneyVo.setBalance(flowingMoneyVo.getIncome().subtract(flowingMoneyVo.getExpenditure()));

        return flowingMoneyVo;
    }


    public void exportExcel(String time, HttpServletRequest request, HttpServletResponse response) {

        try {
            TotalVo totalVo = this.selectRoutine(time);
            if (totalVo == null) {
                log.info("当月{}没有流水记录", time);
                return;
            }
            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(water));

// 根据页面index 获取sheet页

            XSSFSheet sheet = wb.getSheetAt(0);
            XSSFRow row = sheet.createRow(2);
            row.createCell(0).setCellValue("合计");
            setCell(totalVo, row);

            for (int i = 0; i < totalVo.getTotalVos().size(); i++) {
                TotalVo totalVo1 = totalVo.getTotalVos().get(i);
                XSSFRow rw = sheet.createRow(i + 3);
                rw.createCell(0).setCellValue(totalVo1.getPaymentTime().toString());
                setCell(totalVo1, rw);

            }

// 输出Excel文件
            String fileName = "月每日流水统计.xlsx";

            ExportUtil.exportExcel(request, response, fileName, wb);

        } catch (Exception e) {

            throw new BusinessException("下载失败", e);

        }

    }

    private void setCell2(TotalVo totalVo, XSSFRow row) {
        row.createCell(2).setCellValue(totalVo.getRoutineMoney().doubleValue());
        row.createCell(3).setCellValue(totalVo.getRoutineDeduction().doubleValue());
        row.createCell(4).setCellValue(totalVo.getRoutineNet().doubleValue());
        row.createCell(5).setCellValue(totalVo.getRoutineCash().doubleValue());
        row.createCell(6).setCellValue(totalVo.getRoutinePreStorage().doubleValue());
        row.createCell(7).setCellValue(totalVo.getRoutineCurrentDeposit().doubleValue());
        row.createCell(8).setCellValue(totalVo.getOtherMoney().doubleValue());
        row.createCell(9).setCellValue(totalVo.getOtherDeduction().doubleValue());
        row.createCell(10).setCellValue(totalVo.getOtherNet().doubleValue());
        row.createCell(11).setCellValue(totalVo.getOtherCash().doubleValue());
        row.createCell(12).setCellValue(totalVo.getOtherPreStorage().doubleValue());
        row.createCell(13).setCellValue(totalVo.getOtherCurrentDeposit().doubleValue());
        row.createCell(14).setCellValue(totalVo.getDepositIncome().doubleValue());
        row.createCell(15).setCellValue(totalVo.getRefundOfDeposit().doubleValue());
        row.createCell(16).setCellValue(totalVo.getProTotal().doubleValue());
        row.createCell(17).setCellValue(totalVo.getCash().doubleValue());
    }

    private void setCell(TotalVo totalVo, XSSFRow row) {
        row.createCell(1).setCellValue(totalVo.getRoutineMoney().doubleValue());
        row.createCell(2).setCellValue(totalVo.getRoutineDeduction().doubleValue());
        row.createCell(3).setCellValue(totalVo.getRoutineNet().doubleValue());
        row.createCell(4).setCellValue(totalVo.getRoutineCash().doubleValue());
        row.createCell(5).setCellValue(totalVo.getRoutinePreStorage().doubleValue());
        row.createCell(6).setCellValue(totalVo.getRoutineCurrentDeposit().doubleValue());
        row.createCell(7).setCellValue(totalVo.getOtherMoney().doubleValue());
        row.createCell(8).setCellValue(totalVo.getOtherDeduction().doubleValue());
        row.createCell(9).setCellValue(totalVo.getOtherNet().doubleValue());
        row.createCell(10).setCellValue(totalVo.getOtherCash().doubleValue());
        row.createCell(11).setCellValue(totalVo.getOtherPreStorage().doubleValue());
        row.createCell(12).setCellValue(totalVo.getOtherCurrentDeposit().doubleValue());
        row.createCell(13).setCellValue(totalVo.getDepositIncome().doubleValue());
        row.createCell(14).setCellValue(totalVo.getRefundOfDeposit().doubleValue());
        row.createCell(15).setCellValue(totalVo.getProTotal().doubleValue());
        row.createCell(16).setCellValue(totalVo.getCash().doubleValue());
    }


    public void downOwner(HttpServletRequest request, HttpServletResponse response, Integer villageId, String startTime, String endTime) {
        try {

            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(statistics));
// 根据页面index 获取sheet页
            XSSFSheet sheet = wb.getSheetAt(0);
            //设置小区名称
            XSSFRow row = sheet.createRow(0);
            Village village = villageService.getVillage(villageId);
            String name = village.getName();
            //row.createCell(0).setCellValue("小区名称");
            row.createCell(0).setCellValue(name + startTime + "至" + endTime + "财务统计");

            //房屋维修费
            TotalVo repair = flowingWaterMapper.getCount(2, 6, villageId, startTime, endTime);
            setCell(sheet, repair, 3, "房屋维修费");
            //设备维修费
            TotalVo equipment = flowingWaterMapper.getCount(2, 7, villageId, startTime, endTime);
            setCell(sheet, equipment, 4, "设备维修费");
            //其他收入
            TotalVo other = flowingWaterMapper.getCount(1, 10, villageId, startTime, endTime);
            setCell(sheet, other, 5, "其他收入");
            //其他支出
            TotalVo other1 = flowingWaterMapper.getCount(2, 10, villageId, startTime, endTime);
            setCell(sheet, other1, 6, "其他支出");
            //合计
            TotalVo countTotal = flowingWaterMapper.getCountTotal(null, villageId, startTime, endTime);
            setCell(sheet, countTotal, 7, "合计");
            //查找预存
            TotalVo totalVo = flowingWaterMapper.selectProByTime(villageId, startTime, endTime);
            XSSFRow row1 = sheet.getRow(11);
            if (row1 == null) {
                row1 = sheet.createRow(11);
            }
            BigDecimal add = new BigDecimal(0);
            if (totalVo != null) {
                add = totalVo.getProTotal().add(countTotal.getSmall());
            }
            row1.createCell(0).setCellValue(add.doubleValue());
            //查找总预存
            BigDecimal routinePreStorage = new BigDecimal(0);
            if (countTotal != null) {
                routinePreStorage = countTotal.getRoutinePreStorage();
            }
            row1.createCell(1).setCellValue(routinePreStorage.doubleValue());
            BigDecimal subtract = add.subtract(routinePreStorage);
            row1.createCell(2).setCellValue(subtract.doubleValue());
            row1.createCell(3).setCellValue(walletService.find(villageId).doubleValue());

            //设置押金
            deposit(villageId, startTime, endTime, sheet, 14, 4, 5, "装修押金");
            deposit(villageId, startTime, endTime, sheet, 15, 8, 9, "房屋押金");

            String fileName = Optional.ofNullable(village).map(Village::getName).orElse("") + startTime + "-" + endTime + "财务报表.xlsx";

            ExportUtil.exportExcel(request, response, fileName, wb);
        } catch (Exception e) {
            log.info("财务报表下载错误:{}", String.valueOf(e));
            throw new BusinessException("财务报表下载失败");
        }

    }

    private void deposit(Integer villageId, String startTime, String endTime, XSSFSheet sheet, int i, Integer type, Integer type2, String typeName) {
        XSSFRow row3 = sheet.getRow(i);
        if (row3 == null) {
            row3 = sheet.createRow(i);
        }
        row3.createCell(0).setCellValue(typeName);
        // BigDecimal money = depositService.getMoney(startTime, endTime, villageId, type);
        TotalVo other = flowingWaterMapper.getCount(1, type, villageId, startTime, endTime);
        BigDecimal money = new BigDecimal(0);
        if (other != null) {
            BigDecimal otherNet = other.getRoutineMoney();
            if (otherNet != null) {
                money = otherNet;
            }

        }

        TotalVo other2 = flowingWaterMapper.getCount(2, type2, villageId, startTime, endTime);
        BigDecimal otherNet = new BigDecimal(0);
        if (other2 != null) {
            if (other2.getRoutineMoney() != null) {
                otherNet = other.getRoutineMoney();
            }
        }
        row3.createCell(1).setCellValue(money.doubleValue());
        row3.createCell(2).setCellValue(otherNet.doubleValue());
        row3.createCell(3).setCellValue(money.subtract(otherNet).doubleValue());
    }

    private void setCell(XSSFSheet sheet, TotalVo count, int i, String name) {
        XSSFRow row1 = sheet.createRow(i);
        if (row1 == null) {
            row1 = sheet.createRow(i);
        }
        if (count == null) {
            row1.createCell(0).setCellValue(name);
            row1.createCell(1).setCellValue(0);
            row1.createCell(2).setCellValue(0);
            row1.createCell(3).setCellValue(0);
        } else {
            row1.createCell(0).setCellValue(name);
            row1.createCell(1).setCellValue(count.getRoutineMoney().doubleValue());
            row1.createCell(2).setCellValue(count.getRoutineDeduction().doubleValue());
            row1.createCell(3).setCellValue(count.getRoutineNet().doubleValue());
        }

    }

    public ProBuildingVo findProBuildingVo(Integer buildingId, WalletType walletType) {
        return buildingService.findProBuildingVo(buildingId, walletType);
    }

    //新增预收
    @Transactional(rollbackFor = Exception.class)
    public PayBillVo addPro(Integer userId, Integer villageId, Integer buildingId, BigDecimal money, String printing, String payType, String remark, WalletType walletType) {
        User user = userService.getUser(userId);
        //根据楼栋id查出钱包，新增预存总收入，并返回结余
        BigDecimal deposit = walletService.deposit(buildingId, money, walletType);
        String billNo = snowflakeIdWorker.nextId() + "";

        //新增预收流水
        Integer id = this.add(villageId, null, new BigDecimal(0), money, new BigDecimal(0), getFeeName(1), 1, buildingService.findAddress(buildingId),
                patee, 3, null, null, user, remark, buildingId, new BigDecimal(0), money, payType, billNo, walletType);
        //修改楼栋折扣
        //buildingService.updateDiscount(buildingId, discount);
        //打印票据
        if ("1".equals(printing)) {

            Village village = villageService.getVillage(villageId);

            BuildingVo building = buildingService.findBuilding(buildingId);

            OwnerEntity ownerEntity = ownerService.findByBuildingId(buildingId);

            PayBillVo payBillVo = new PayBillVo();

            payBillVo.setTitle(patee + "收款收据");

            payBillVo.setBillNo(billNo);

            payBillVo.setVillageName(village.getName());

            payBillVo.setAddress(building.getBuilding() + "-" + building.getUnit() + "-" + building.getRoomNumber());
            if (ownerEntity != null) {
                payBillVo.setOwnerName(ownerEntity.getOwnerName());
            }

            payBillVo.setPayTime(LocalDate.now());

            payBillVo.setFeeType("预收款");

            payBillVo.setMoney(money);

            payBillVo.setAdvanceBalance(deposit);

            payBillVo.setRemark(remark);

            payBillVo.setRealAmount(money);

            payBillVo.setChineseAmount(NumberToCn.number2Cn(money));

            payBillVo.setPayee(user.getUserName());

            payBillVo.setReceiveUnit(receiveUnit);

            String wt = "";
            if (walletType == null || WalletType.PROPERTY.equals(walletType)) {
                wt = "物业费";
            } else if (WalletType.RENT.equals(walletType)) {
                wt = "房屋租金";
            } else {
                wt = "车位租金";
            }

            payBillVo.setWalletType(wt);
            return payBillVo;
        }

        return null;
    }

    @Transactional(rollbackFor = Exception.class)
    public void deletePro(Integer id) {
        FlowingWaterEntity entity = getNormalProById(id);
        BigDecimal money = entity.getNet();
        WalletType walletType = null;
        if (entity.getWalletType() != null) {
            walletType = WalletType.valueOf(entity.getWalletType());
        }
        walletService.updateExpenditure(entity.getBuildingId(), money, walletType);

        deleteFlowingWater(id);
    }

    private FlowingWaterEntity getNormalProById(Integer id) {
        QueryWrapper<FlowingWaterEntity> wrapper = new QueryWrapper<>();
        wrapper.eq("id", id);
        wrapper.eq("type", "3");
        wrapper.eq("state", FlowingWaterEntity.STATE_NORMAL);

        return Optional.ofNullable(flowingWaterMapper.selectOne(wrapper)).orElseThrow(() -> new BusinessException("预收费记录不存在", String.format("id: %s", id)));
    }

    private void deleteFlowingWater(Integer id) {
        FlowingWaterEntity entity = new FlowingWaterEntity();
        entity.setId(id);
        entity.setState(FlowingWaterEntity.STATE_DELETED);

        flowingWaterMapper.updateById(entity);
    }

    public void updateBillNo(Integer id, String billNo) {
        UpdateWrapper<FlowingWaterEntity> wrapper = new UpdateWrapper<>();
        wrapper.set("bill_no", billNo);
        wrapper.eq("id", id);
        flowingWaterMapper.update(null, wrapper);
    }

    public String getFeeName(Integer feeType) {
        switch (feeType) {
            case 1:
                return "物业管理费";
            case 2:
                return "房屋租金";
            case 3:
                return "车位租金";
            case 4:
                return "装修押金缴纳";
            case 5:
                return "装修押金退还";
            case 6:
                return "维修费用";
            default:
                return "未知";
        }
    }

    @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRES_NEW)
    public void propertyDeduction(User user, Village village, Building building, Integer year, Integer month, BigDecimal shouldPay, BigDecimal realFee) {
        BigDecimal subtractFee = shouldPay.subtract(realFee);


        BigDecimal expenditure = walletService.updateExpenditure(building.getId(), realFee, null);

        String explain = year + (month >= 10 ? month + "" : "0" + month) + "物业费批量划扣";
        String listOfCharges = "物业管理费";
        String billNo = snowflakeIdWorker.nextId() + "";
        FlowingWaterEntity entity = createFlowingWaterEntity(user, building, shouldPay, realFee, subtractFee, realFee, expenditure, BigDecimal.ZERO, BigDecimal.ZERO, listOfCharges, 1, explain, "批量划扣", SysSet.PAY_TYPE_CODE_FOUR, billNo, FlowingWaterEntity.OTHER_TYPE_NORMAL);
        flowingWaterMapper.insert(entity);

    }

    public FlowingWaterEntity createFlowingWaterEntity(User user, Building building, BigDecimal shouldPay, BigDecimal realFee, BigDecimal subtractFee, BigDecimal preStorage, BigDecimal expenditure, BigDecimal cash, BigDecimal small, String listOfCharges, Integer type, String explain, String remarks, String payType, String billNo, Byte otherType) {
        FlowingWaterEntity entity = new FlowingWaterEntity();
        entity.setPayType(payType);
        entity.setExplain(explain);
        entity.setRemarks(remarks);
        entity.setDrawer(user.getUserName());
        entity.setMoney(shouldPay);
        entity.setNet(realFee);
        entity.setDeduction(subtractFee);
        entity.setPreStorage(preStorage);
        entity.setCurrentDeposit(expenditure);
        entity.setCash(cash);
        entity.setListOfCharges(listOfCharges);
        entity.setChargeType(1);
        entity.setPayer(buildingService.findAddress(building.getId()));
        entity.setPayee(patee);
        entity.setType(type);
        entity.setPaymentTime(DateUtilPlus.getCurrentLocalDate());
        entity.setVillageId(building.getVillageId());
        entity.setBuildingId(building.getId());
        entity.setDiscount(building.getDiscount());
        entity.setSmall(small);
        entity.setBillNo(billNo);
        entity.setOtherType(otherType);
        entity.setState(FlowingWaterEntity.STATE_NORMAL);
        return entity;
    }

    public FlowingWaterExpandEntity createFlowingWaterEntityByBuildingId(User user, Integer buildingId, BigDecimal shouldPay, BigDecimal realFee, BigDecimal subtractFee, BigDecimal preStorage, BigDecimal expenditure, BigDecimal cash, BigDecimal small, String listOfCharges, Integer type, String explain, String remarks, String payType, String billNo, Byte otherType) {
        FlowingWaterExpandEntity entity = new FlowingWaterExpandEntity();
        entity.setPayType(payType);
        entity.setExplain(explain);
        entity.setRemarks(remarks);
        entity.setDrawer(user.getUserName());
        entity.setMoney(shouldPay);
        entity.setNet(realFee);
        entity.setDeduction(subtractFee);
        entity.setPreStorage(preStorage);
        entity.setCurrentDeposit(expenditure);
        entity.setCash(cash);
        entity.setListOfCharges(listOfCharges);
        entity.setChargeType(1);
        entity.setPayer(buildingService.findAddress(buildingId));
        entity.setPayee(patee);
        entity.setType(type);
        entity.setPaymentTime(LocalDate.now());
        BuildingVo building = buildingService.findBuilding(buildingId);
        entity.setVillageId(building.getVillageId());
        entity.setBuildingId(building.getId());
        entity.setDiscount(building.getDiscount());
        entity.setSmall(small);
        entity.setBillNo(billNo);
        entity.setOtherType(otherType);
        entity.setState(FlowingWaterEntity.STATE_NORMAL);
        return entity;
    }


    public Integer save(FlowingWaterEntity entity) {
        flowingWaterMapper.insert(entity);
        return entity.getId();
    }

    //每日流水统计
    public TotalVo selectRoutine(String time) {
        String start = time + "-01";
        String end = null;
        if (time.contains("-01") || time.contains("-03") || time.contains("-05") || time.contains("-07") || time.contains("-08") || time.contains("-10") || time.contains("-12")) {
            end = time + "-31";
        } else if (time.contains("-02")) {
            end = time + "-28";
        } else {
            end = time + "-30";
        }
//        String end = time + "-31";

        //常规收费
        TotalVo totalVo = getTotal(start, end, null);
        /*查询明细*/
        //查询月份中哪些日期有入账
        List<String> strings = flowingWaterMapper.selectTime(start, end);
        List<String> list = depositService.findTime(start, end);
        if (strings.isEmpty() && list.isEmpty()) {
            totalVo.setTotalVos(Collections.emptyList());
        } else {
            strings.addAll(list);
            //去重复
            List<String> collect = strings.stream().distinct().collect(Collectors.toList());
            Collections.sort(collect, Collections.reverseOrder());
            //遍历集合  分日期统计
            List<TotalVo> totalVos = new ArrayList<>();
            for (String s : collect) {
                //按日期统计常规费用
                TotalVo vo = new TotalVo();
                TotalVo vos = flowingWaterMapper.selectRoutineDetailed(s);
                if (vos != null) {
                    vo.setPaymentTime(vos.getPaymentTime());
                    setRoutie(vo, vos);
                }
                //按日期统计常规
                TotalVo otherDetailed = flowingWaterMapper.selectOtherDetailed(s);
                if (otherDetailed != null) {
                    vo.setPaymentTime(otherDetailed.getPaymentTime());
                    setOther(vo, otherDetailed);
                }
                //按日期统计押金
                BigDecimal detailed = depositService.getMoneyDetailed(s);
                if (detailed.compareTo(new BigDecimal(0)) > 0) {
                    vo.setPaymentTime(DateUtil.getLocalDate(s));
                    vo.setDepositIncome(detailed);
                }
                BigDecimal decimal = depositService.getRefundDetailed(s);
                if (decimal.compareTo(new BigDecimal(0)) > 0) {
                    vo.setRefundOfDeposit(decimal);
                    vo.setPaymentTime(DateUtil.getLocalDate(s));
                }

                //按日期统计当前预存
                TotalVo vo1 = flowingWaterMapper.selectProDetailed(s);
                BigDecimal proTotal = new BigDecimal(0);
                if (vo1 != null) {
                    vo.setPaymentTime(vo1.getPaymentTime());
                    proTotal = vo1.getProTotal();
                    vo.setProTotal(proTotal);
                }
                //统计当日现金
                BigDecimal add = proTotal.add(vo.getRoutineCash()).add(vo.getOtherCash());
                vo.setCash(add);
                totalVos.add(vo);
            }
            totalVo.setTotalVos(totalVos);
        }
        totalVo.setCash(totalVo.getRoutineCash().add(totalVo.getOtherCash()).add(totalVo.getProTotal()));
        return totalVo;
    }

    private TotalVo getTotal(String start, String end, Integer villageId) {
        TotalVo totalVo = new TotalVo();
        TotalVo routine = flowingWaterMapper.selectRoutine(start, end, villageId);
        if (routine != null) {
            setRoutie(totalVo, routine);
        }
        //其他收费
        TotalVo other = flowingWaterMapper.selectOther(start, end, villageId);
        if (other != null) {
            setOther(totalVo, other);
        }
        //押金
        totalVo.setDepositIncome(depositService.getMoney(start, end, villageId, null));
        //退返押金
        totalVo.setRefundOfDeposit(depositService.getRefund(start, end, villageId, null));
        //查当月预存总数
        totalVo.setProTotal(flowingWaterMapper.selectPro(start, end, villageId).getProTotal());
        return totalVo;
    }

    private void setRoutie(TotalVo totalVo, TotalVo routine) {
        totalVo.setRoutineCash(routine.getRoutineCash());
        totalVo.setRoutineCurrentDeposit(routine.getRoutineCurrentDeposit());
        totalVo.setRoutineDeduction(routine.getRoutineDeduction());
        totalVo.setRoutineMoney(routine.getRoutineMoney());
        totalVo.setRoutineNet(routine.getRoutineNet());
        totalVo.setRoutinePreStorage(routine.getRoutinePreStorage());
    }

    private void setOther(TotalVo totalVo, TotalVo other) {
        totalVo.setOtherCash(other.getOtherCash());
        totalVo.setOtherCurrentDeposit(other.getOtherCurrentDeposit());
        totalVo.setOtherDeduction(other.getOtherDeduction());
        totalVo.setOtherMoney(other.getOtherMoney());
        totalVo.setOtherNet(other.getOtherNet());
        totalVo.setOtherPreStorage(other.getOtherPreStorage());
    }


    public TotalVo selectRoutine(String startTime, String endTime, Integer villageId) {
        log.info("下载每栋统计流水。。。。。。。。。。。。。。。。。");
        //获取合计数
        TotalVo total = getTotal(startTime, endTime, villageId);
        //设置明细
        List<TotalVo> totalVos = new ArrayList<>();
        //判断是否根据小区筛选
        if (villageId == null) {
            List<Integer> villageIds = villageService.getVillageId();
            if (villageIds.isEmpty()) {
                return total;
            }
            //根据小区id 查询楼栋分类
            for (Integer integer : villageIds) {
                getTotalByVillageId(startTime, endTime, total, totalVos, integer);
            }
        } else {
            getTotalByVillageId(startTime, endTime, total, totalVos, villageId);
        }
        total.setCash(total.getRoutineCash().add(total.getOtherCash().add(total.getProTotal())));
        total.setTotalVos(totalVos);
        return total;
    }

    public MonthStatisticVo monthStatisticVo(Integer villageId, String month) {

        //2020-12 to 202012
        String month1 = month.substring(0, 4) + month.substring(5);

        //获取房间信息
        List<MonthStatisticVo.Detail> unitRooms = flowingWaterMapper.unitRooms(villageId);

        //TODO 房屋出租和车位的这里暂时不写，可能没有
        List<MonthStatisticVo.Detail> unitPayedList = flowingWaterMapper.unitPayed(villageId, month, month1);

        //获取欠费信息
        List<MonthStatisticVo.Detail> unitOwns = flowingWaterMapper.unitOwn(villageId, month, month1);

        //统计欠费补缴
        List<MonthStatisticVo.Detail> unitOwnPayment = flowingWaterMapper.unitOwnPayment(villageId, month);

        return mergeData(unitRooms, unitPayedList, unitOwns, unitOwnPayment);
    }

    /**
     * 合并数据
     *
     * @param unitRooms
     * @param unitPayedList
     * @param unitOwns
     * @return
     */
    private MonthStatisticVo mergeData(List<MonthStatisticVo.Detail> unitRooms, List<MonthStatisticVo.Detail> unitPayedList, List<MonthStatisticVo.Detail> unitOwns, List<MonthStatisticVo.Detail> unitOwnPayment) {


        MonthStatisticVo statistic = new MonthStatisticVo();

        statistic.setTotalShouldPay(BigDecimal.ZERO);
        statistic.setTotalDeduction(BigDecimal.ZERO);
        statistic.setTotalOwn(BigDecimal.ZERO);
        statistic.setTotalPayed(BigDecimal.ZERO);
        statistic.setTotalNormalPayment(BigDecimal.ZERO);
        statistic.setTotalRooms(0);
        statistic.setTotalOwnPayment(BigDecimal.ZERO);
        statistic.setTotalOwnDeduction(BigDecimal.ZERO);

        //以房屋为单位
        for (MonthStatisticVo.Detail unitRoom : unitRooms) {

            statistic.setTotalRooms(statistic.getTotalRooms() + unitRoom.getRooms());

            //已减免
            unitRoom.setDeduction(BigDecimal.ZERO);
            //欠费款
            unitRoom.setOwn(BigDecimal.ZERO);
            //应收款
            unitRoom.setShouldPay(BigDecimal.ZERO);
            //欠费补缴
            unitRoom.setOwnPayment(BigDecimal.ZERO);
            //常规收款
            unitRoom.setNormalPayment(BigDecimal.ZERO);
            //已收款
            unitRoom.setPayed(BigDecimal.ZERO);
            //欠费减免额度
            unitRoom.setOwnDeduction(BigDecimal.ZERO);
            //遍历已付款
            for (MonthStatisticVo.Detail unitPayed : unitPayedList) {
                //当房间号匹配命中则更新其费用
                if (unitPayed.getBuilding().equals(unitRoom.getBuilding()) && unitPayed.getUnit().equals(unitRoom.getUnit())) {
                    unitRoom.setNormalPayment(unitPayed.getPayed());
                    unitRoom.setDeduction(unitPayed.getDeduction());

                    statistic.setTotalNormalPayment(statistic.getTotalNormalPayment().add(unitPayed.getPayed()));
                    statistic.setTotalDeduction(statistic.getTotalDeduction().add(unitPayed.getDeduction()));
                }
            }

            for (MonthStatisticVo.Detail unitOwn : unitOwns) {
                //房间命中则更新其欠款
                if (unitOwn.getBuilding().equals(unitRoom.getBuilding()) && unitOwn.getUnit().equals(unitRoom.getUnit())) {
                    unitRoom.setOwn(unitOwn.getOwn());
                    statistic.setTotalOwn(statistic.getTotalOwn().add(unitOwn.getOwn()));
                }
            }

            for (MonthStatisticVo.Detail unitOwn : unitOwnPayment) {
                //房间命中则更新其欠款
                if (unitOwn.getBuilding().equals(unitRoom.getBuilding()) && unitOwn.getUnit().equals(unitRoom.getUnit())) {
                    unitRoom.setOwnPayment(unitOwn.getOwnPayment());
                    unitRoom.setOwnDeduction(unitOwn.getOwnDeduction());
                    statistic.setTotalOwnPayment(statistic.getTotalOwnPayment().add(unitOwn.getOwnPayment()));
                    statistic.setTotalOwnDeduction(statistic.getTotalOwnDeduction().add(unitOwn.getOwnDeduction()));
                }
            }

            BigDecimal payed = unitRoom.getNormalPayment().add(unitRoom.getOwnPayment());
            unitRoom.setPayed(payed);
        }

        //更新房间的应付金额
        for (MonthStatisticVo.Detail unitRoom : unitRooms) {

            unitRoom.setShouldPay(unitRoom.getNormalPayment().add(unitRoom.getDeduction()).add(unitRoom.getOwn()));
        }
        statistic.setTotalPayed(statistic.getTotalNormalPayment().add(statistic.getTotalOwnPayment()));
        statistic.setTotalShouldPay(statistic.getTotalNormalPayment().add(statistic.getTotalDeduction()).add(statistic.getTotalOwn()));
        statistic.setDetails(unitRooms);
        return statistic;


    }

    private void getTotalByVillageId(String startTime, String endTime, TotalVo total, List<TotalVo> totalVos, Integer integer) {
        List<Integer> buildings = buildingService.selectBuilding(integer);
        Village village = villageService.getVillage(integer);
        String name = village.getName();
        if (buildings.isEmpty()) {
            return;
        }
        //根据楼栋 分类查找楼栋id
        for (Integer building1 : buildings) {
            //楼栋列表页id
            List<Integer> buildingIdVos = buildingService.selectBuilding(integer, building1);
            if (buildingIdVos.isEmpty()) {
                continue;
            }
            TotalVo vo = new TotalVo();
            //设置楼栋
            vo.setBuildingAddress(name + "-" + building1 + "栋");
            TotalVo totalVo = flowingWaterMapper.selectRoutineByBuildingId(startTime, endTime, buildingIdVos);
            if (totalVo != null) {
                vo.setVillageName(totalVo.getVillageName());
                setRoutie(vo, totalVo);
            }
            //统计其他费用
            TotalVo other = flowingWaterMapper.selectOtherByBuildingId(startTime, endTime, buildingIdVos);
            if (other != null) {
                vo.setVillageName(other.getVillageName());
                setOther(vo, other);
            }
            //统计押金
            TotalVo money = depositService.getMoneyByBuildingId(startTime, endTime, buildingIdVos);
            if (money != null) {
                vo.setVillageName(money.getVillageName());
                vo.setDepositIncome(money.getDepositIncome());
            }
            //统计退款押金
            TotalVo refund = depositService.getRefundByBuildingId(startTime, endTime, buildingIdVos);
            if (refund != null) {
                vo.setVillageName(refund.getVillageName());
                vo.setRefundOfDeposit(refund.getRefundOfDeposit());
            }
            //统计预存
            TotalVo pro = flowingWaterMapper.selectProByBuildingId(startTime, endTime, buildingIdVos);
            if (pro != null) {
                vo.setProTotal(pro.getProTotal());
                vo.setVillageName(pro.getVillageName());
            }
            if (vo.getVillageName() != null) {
                BigDecimal add = vo.getProTotal().add(vo.getRoutineCash()).add(vo.getOtherCash());
                vo.setCash(add);
                totalVos.add(vo);
            }
        }
    }

    //栋流水导出
    public void exportExcel(String startTime, String endTime, Integer villageId, HttpServletRequest request, HttpServletResponse response) {
        log.info("文件路径是{}", building);
        try {
            TotalVo totalVo = this.selectRoutine(startTime, endTime, villageId);
            if (totalVo == null) {
                log.info("没有流水记录");
                return;
            }
            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(building));
            log.info("wb的值{}", wb);
// 根据页面index 获取sheet页

            XSSFSheet sheet = wb.getSheetAt(0);
            XSSFRow row = sheet.createRow(2);
            row.createCell(0).setCellValue("合计");
            setCell2(totalVo, row);

            for (int i = 0; i < totalVo.getTotalVos().size(); i++) {
                TotalVo totalVo1 = totalVo.getTotalVos().get(i);
                XSSFRow rw = sheet.createRow(i + 3);
                rw.createCell(0).setCellValue(totalVo1.getVillageName());
                rw.createCell(1).setCellValue(totalVo1.getBuildingAddress());
                setCell2(totalVo1, rw);

            }

// 输出Excel文件
            String fileName = "每栋流水统计.xlsx";

            ExportUtil.exportExcel(request, response, fileName, wb);
        } catch (Exception e) {

            throw new BusinessException("下载失败", e);

        }

    }

    public void exportMonthStatistic(Integer villageId, String month, HttpServletRequest request, HttpServletResponse response) {

        List<ArrearsOwnVo> arrearsVos = arrearsDetailMapper.listState();
        List<Integer> idList = new ArrayList<>();

        for (ArrearsOwnVo arrearsVo : arrearsVos) {
            Integer flowingWaterId = arrearsVo.getFlowingWaterId();
            if (!idList.contains(flowingWaterId)){
                FlowingWaterVo flowingWaterVo = flowingWaterMapper.getById(flowingWaterId);
                LocalDate paymentTime = flowingWaterVo.getPaymentTime();
                UpdateWrapper<ArrearsDetailEntity> wrapper = new UpdateWrapper<>();
                wrapper.eq("flowing_water_id", flowingWaterId);
                DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM");
                String format = paymentTime.format(dateTimeFormatter);
                wrapper.set("charge_time", format);
                arrearsDetailMapper.update(null, wrapper);
                idList.add(flowingWaterId);
            }


        }
        System.out.println("sas");
/*        try {
            MonthStatisticVo monthStatisticVo = this.monthStatisticVo(villageId, month);
            Village village = villageService.getVillage(villageId);
            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(monthStatisticTemplate));
            // 根据页面index 获取sheet页

            XSSFSheet sheet = wb.getSheetAt(0);

            XSSFRow row = sheet.getRow(0);

            row.getCell(0).setCellValue(village.getName() + "(" + month + ")");

            for (int i = 0; i < monthStatisticVo.getDetails().size(); i++) {
                final MonthStatisticVo.Detail detail = monthStatisticVo.getDetails().get(i);
                XSSFRow rw = sheet.createRow(i + 2);
                rw.createCell(0).setCellValue(detail.getBuilding());
                rw.createCell(1).setCellValue(detail.getUnit());
                rw.createCell(2).setCellValue(detail.getRooms());
                rw.createCell(3).setCellValue(detail.getShouldPay().toString());
                rw.createCell(4).setCellValue(detail.getPayed().toString());
                rw.createCell(5).setCellValue(detail.getNormalPayment().toString());
                rw.createCell(6).setCellValue(detail.getDeduction().toString());
                rw.createCell(7).setCellValue(detail.getOwnPayment().toString());
                rw.createCell(8).setCellValue(detail.getOwnDeduction().toString());
                rw.createCell(9).setCellValue(detail.getOwn().toString());

            }

            row = sheet.createRow(monthStatisticVo.getDetails().size() + 2);

            row.createCell(0).setCellValue("合计");
            row.createCell(2).setCellValue(monthStatisticVo.getTotalRooms());
            row.createCell(3).setCellValue(monthStatisticVo.getTotalShouldPay().toString());
            row.createCell(4).setCellValue(monthStatisticVo.getTotalPayed().toString());
            row.createCell(5).setCellValue(monthStatisticVo.getTotalNormalPayment().toString());
            row.createCell(6).setCellValue(monthStatisticVo.getTotalDeduction().toString());
            row.createCell(7).setCellValue(monthStatisticVo.getTotalOwnPayment().toString());
            row.createCell(8).setCellValue(monthStatisticVo.getTotalOwnDeduction().toString());
            row.createCell(9).setCellValue(monthStatisticVo.getTotalOwn().toString());

            String fileName = village.getName() + month + "月报表统计.xlsx";

            ExportUtil.exportExcel(request, response, fileName, wb);
        } catch (Exception e) {

            throw new BusinessException("下载失败", e);

        }*/
    }

    public PayBillVo printBill(Integer id) {
        FlowingWaterEntity entity = getById(id);
        Village village = villageService.getVillage(entity.getVillageId());
        Building building = null;
        OwnerEntity ownerEntity = null;
        if (entity.getBuildingId() != null) {
            building = buildingService.findBuilding(entity.getBuildingId());
            ownerEntity = ownerService.findByBuildingId(entity.getBuildingId());
        }
        PayBillVo payBillVo = new PayBillVo();
        payBillVo.setTitle(patee + "收款收据");

        payBillVo.setBillNo(entity.getBillNo());

        payBillVo.setVillageName(village.getName());

        if (building != null) {
            payBillVo.setAddress(building.getBuilding() + "-" + building.getUnit() + "-" + building.getRoomNumber());
        }
        if (ownerEntity != null) {
            payBillVo.setOwnerName(ownerEntity.getOwnerName());
        }

        payBillVo.setPayTime(entity.getPaymentTime());

        payBillVo.setFeeType(sysSetService.getDesc(SysSet.COLLECTION_TYPE, entity.getChargeType().toString()));

        payBillVo.setMoney(entity.getNet());

        payBillVo.setExplain(entity.getExplain());

        payBillVo.setRemark(entity.getRemarks());

        payBillVo.setRealAmount(entity.getCash());

        payBillVo.setChineseAmount(NumberToCn.number2Cn(entity.getCash()));

        payBillVo.setPayer(entity.getPayer());

        payBillVo.setPayee(entity.getDrawer());

        payBillVo.setReceiveUnit(entity.getPayee());

/*        WalletType walletType = WalletType.valueOf(entity.getWalletType());

        String wt = "";
        if (walletType == null || WalletType.PROPERTY.equals(walletType)) {
            wt = "物业费";
        } else if (WalletType.RENT.equals(walletType)) {
            wt = "房屋租金";
        } else {
            wt = "车位租金";
        }*/
        return payBillVo;

    }

    public PayBillVo propertyPrintBill(Integer id) {
        FlowingWaterEntity entity = getById(id);
        Village village = villageService.getVillage(entity.getVillageId());
        Building building = null;
        OwnerEntity ownerEntity = null;
        if (entity.getBuildingId() != null) {
            building = buildingService.findBuilding(entity.getBuildingId());
            ownerEntity = ownerService.findByBuildingId(entity.getBuildingId());
        }
        PayBillVo payBillVo = new PayBillVo();
        payBillVo.setTitle(patee + "收款收据");

        payBillVo.setBillNo(entity.getBillNo());

        payBillVo.setVillageName(village.getName());

        if (building != null) {
            payBillVo.setAddress(building.getBuilding() + "-" + building.getUnit() + "-" + building.getRoomNumber());
        }
        if (ownerEntity != null) {
            payBillVo.setOwnerName(ownerEntity.getOwnerName());
        }

        payBillVo.setPayTime(entity.getPaymentTime());

        payBillVo.setFeeType("物业管理费");

        payBillVo.setShouldPay(entity.getMoney());


        payBillVo.setDeductionAmount(entity.getDeduction());

        payBillVo.setMoney(entity.getNet());

        payBillVo.setAdvanceBalance(entity.getCurrentDeposit());

        payBillVo.setExplain(entity.getExplain());

        payBillVo.setRemark(entity.getRemarks());

        payBillVo.setRealAmount(entity.getCash());

        payBillVo.setChineseAmount(NumberToCn.number2Cn(entity.getCash()));

        payBillVo.setPayee(entity.getDrawer());

        payBillVo.setReceiveUnit(receiveUnit);

        return payBillVo;

    }

    public PayBillVo advancePrintBill(Integer id) {
        FlowingWaterEntity entity = getById(id);
        Village village = villageService.getVillage(entity.getVillageId());

        BuildingVo building = buildingService.findBuilding(entity.getBuildingId());

        OwnerEntity ownerEntity = ownerService.findByBuildingId(entity.getBuildingId());

        PayBillVo payBillVo = new PayBillVo();

        payBillVo.setTitle(patee + "收款收据");

        payBillVo.setBillNo(entity.getBillNo());

        payBillVo.setVillageName(village.getName());

        payBillVo.setAddress(building.getBuilding() + "-" + building.getUnit() + "-" + building.getRoomNumber());
        if (ownerEntity != null) {
            payBillVo.setOwnerName(ownerEntity.getOwnerName());
        }

        payBillVo.setPayTime(LocalDate.now());

        payBillVo.setFeeType("预收款");

        payBillVo.setMoney(entity.getNet());

        payBillVo.setAdvanceBalance(entity.getCurrentDeposit());

        payBillVo.setRemark(entity.getRemarks());

        payBillVo.setRealAmount(entity.getNet());

        payBillVo.setChineseAmount(NumberToCn.number2Cn(entity.getNet()));

        payBillVo.setPayee(entity.getDrawer());

        payBillVo.setReceiveUnit(receiveUnit);

        return payBillVo;

    }

    private FlowingWaterEntity getById(Integer id) {
        FlowingWaterEntity flowingWaterEntity = flowingWaterMapper.selectById(id);
        return Optional.ofNullable(flowingWaterEntity).orElseThrow(() -> new SystemException("缴费记录不存在", String.format("id[%s]", id)));
    }

    @Autowired
    public void setSnowflakeIdWorker(SnowflakeIdWorker snowflakeIdWorker) {
        this.snowflakeIdWorker = snowflakeIdWorker;
    }

    @Autowired
    public void setOwnerService(OwnerService ownerService) {
        this.ownerService = ownerService;
    }
}
